﻿CREATE PROCEDURE [dbo].[ListActiveLeads]

AS
	SET NOCOUNT ON

	DECLARE @Leads Table (
		LeadKey INT NOT NULL,
		Title nvarChar(127) NOT NULL,
		OwnerKey INT NULL ,
		CreatedByKey INT NOT NULL,
		CreatedDate DateTimeOffset NOT NULL, 
		LeadTypeKey INT NOT NULL, 
		LeadStatusKey INT NOT NULL 
	)

	INSERT INTO @Leads
	SELECT 
		L.LeadKey, 
		L.Title, 
		L.OwnerKey, 
		L.CreatedByKey, 
		L.CreatedDate, 
		L.LeadTypeKey,
		L.LeadStatusKey
	FROM dbo.Lead L
	INNER JOIN dbo.LeadStatus LS 
		ON L.LeadStatusKey = LS.LeadStatusKey
	WHERE 
		LS.IsActive = 1

	SELECT
		LeadKey,
		Title,
		OwnerKey,
		CreatedByKey,
		CreatedDate, 
		LeadTypeKey, 
		LeadStatusKey
	FROM @Leads

	SELECT 
		EditorKey,
		UserName,
		FirstName,
		LastName,
		EmailAddress,
		PasswordSalt,
		PasswordHash,
		IsEnabled
	FROM dbo.Editor
	WHERE 
		EditorKey IN (SELECT DISTINCT OwnerKey FROM @Leads) OR
		EditorKey IN (SELECT DISTINCT CreatedByKey FROM @Leads)


RETURN 0
